package com.mairuide.bill.methodsanalysis.bszjzy.service;

import com.mairuide._frame.utils.ListUtils;
import com.mairuide.bill.methodsanalysis.bszjzy.dto.BszizyDto;
import com.mairuide.bill.methodsanalysis.bszjzy.pojo.BszizyEntity;
import com.mairuide.bill.methodsanalysis.bszjzy.pojo.BszizyEntity_YH;
import com.mairuide.casemanage.service.CaseManageService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.*;

/**
 * @Description
 * @auther 刘中华
 * @create 2019-06-03 14:36
 */
@Service
public class BszizyService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private CaseManageService caseManageService;
    @PersistenceContext
    private EntityManager entityManager;
    private final int POOL_SIZE = Runtime.getRuntime().availableProcessors();
    /**
     * @Author 刘中华
     * @Date 2019/6/3 16:15
     * @Param [dto]
     * @return java.util.List<com.mairuide.bill.methodsanalysis.bszjzy.pojo.BszizyEntity>
     * @Description 伴随资金转移方法
     **/
    public List<BszizyEntity> findBSZJZY(BszizyDto dto){
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String casemanageId = dto.getCasemanageId();
        Integer sign = dto.getSign();
        Integer dtoHour = dto.getHour()==null?1:dto.getHour();
        Integer tradeCount = dto.getTradeCount()==null?3:dto.getTradeCount();
        String accountBodyCard = dto.getAccountBodyCard();
        String beginDate = dto.getBeginDate();
        String endDate = dto.getEndDate();
        String sort = dto.getSort();
        String order = dto.getOrder();
        String whichTable = caseManageService.getWhichTable(casemanageId);
        //第一个查询，查询出所有的交易记录，根据交易时间排序，然后过滤出交易时间为1小时之内的符合条件的记录。
        //存放符合条件的记录id
        //存放符合条件的记录id
        HashSet<Integer> idSet = getID(dto, whichTable);
        //第二个查询，根据第一条查询所得到的id集合为条件，得到最后的结果
        List<Integer> list_1 = new ArrayList<Integer>(idSet);
        List<List<Integer>> listList = ListUtils.splitList4(list_1, 1000);

        List<BszizyEntity> bszizyEntities = new ArrayList<>();

        if (listList!=null){
            for (int i = 0; i < listList.size(); i++) {
                List<Integer> idList = listList.get(i);
                String ids = StringUtils.join(idList, ",");
                String sql2="select * from (SELECT account_body ,account_body_card,account_rival_card, ";
                sql2+=" account_rival , account_rival_bank , sign, sum(abs(account_money)) trade_total_money,count(1) COUNT, ";
                sql2+=" count(DISTINCT to_char( trade_date, 'yyyy-MM-dd' )) AS trade_count ";
                sql2+=" FROM "+whichTable;
                sql2+=" WHERE casemanage_id = '" + casemanageId +"' ";
                sql2+=" and ACCOUNT_RIVAL_CARD is not null " ;
                if (sign !=null){
                    sql2+=" and sign = "+sign;
                }
                if (StringUtils.isNotBlank(accountBodyCard)){
                    sql2+=" and account_body_card = '"+accountBodyCard+"' ";
                }
                if (StringUtils.isNotBlank(beginDate)){
                    sql2+=" and TRADE_DATE >= to_date( '"+beginDate +"' , 'yyyy-mm-dd' ) ";
                }
                if (StringUtils.isNotBlank(endDate)){
                    sql2+=" and TRADE_DATE <= to_date( '"+endDate +"' , 'yyyy-mm-dd' ) ";
                }
                sql2+=" AND BILL_ID in ("+ ids +" ) ";
                sql2+=" GROUP BY account_rival_card,account_body,account_body_card,account_rival,account_rival_bank,sign ) a ";
                sql2+=" WHERE a.trade_count > "+tradeCount;
                if (StringUtils.isNotBlank(sort)) {
                    sql2+=" order by " + sort + " " + order;
                }else{
                    sql2+=" ORDER BY a.trade_count desc";
                }
                List<BszizyEntity> bszizyList = jdbcTemplate.query(sql2,new BeanPropertyRowMapper<BszizyEntity>(BszizyEntity.class));
                bszizyEntities.addAll(bszizyList);
            }
        }
        for (int i = 0; i <bszizyEntities.size() ; i++) {
            BszizyEntity entity = bszizyEntities.get(i);
            String account_body = entity.getACCOUNT_BODY()==null?"":entity.getACCOUNT_BODY();
            String account_body_card = entity.getACCOUNT_BODY_CARD()==null?"":entity.getACCOUNT_BODY_CARD();
            String account_rival = entity.getACCOUNT_RIVAL()==null?"":entity.getACCOUNT_RIVAL();
            String account_rival_card = entity.getACCOUNT_RIVAL_CARD()==null?"":entity.getACCOUNT_RIVAL_CARD();
            String account_rival_bank = entity.getACCOUNT_RIVAL_BANK()==null?"":entity.getACCOUNT_RIVAL_BANK();
            entity.setACCOUNT_BODY(account_body);
            entity.setACCOUNT_BODY_CARD(account_body_card);
            entity.setACCOUNT_RIVAL(account_rival);
            entity.setACCOUNT_RIVAL_CARD(account_rival_card);
            entity.setACCOUNT_RIVAL_BANK(account_rival_bank);
        }
        List<BszizyEntity> set = new ArrayList<>();
        for (int i = 0; i <bszizyEntities.size(); i++) {
            BszizyEntity entity = bszizyEntities.get(i);
            if (!set.contains(entity)){
                set.add(entity);
            }else{
                for (int j = 0; j <set.size() ; j++) {
                    BszizyEntity entity1 = set.get(j);
                    if (entity1.equals(entity)){
                        Integer count = entity.getCOUNT();
                        Integer count1 = entity1.getCOUNT();
                        entity1.setCOUNT(count+count1);
                    }
                }
            }
        }
        return set;
    }
    //伴随资金转移交易总次数 详情
    public List findBSZJZY_detail(BszizyDto dto){
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String casemanageId = dto.getCasemanageId();
        Integer sign = dto.getSign();
        Integer dtoHour = dto.getHour()==null?1:dto.getHour();
        Integer tradeCount = dto.getTradeCount()==null?3:dto.getTradeCount();

        String beginDate = dto.getBeginDate();
        String endDate = dto.getEndDate();
        String sort = dto.getSort();
        String order = dto.getOrder();

        String accountBodyCard = dto.getAccountBodyCard();
        String accountBody = dto.getAccountBody();
        String accountRival = dto.getAccountRival();
        String accountRivalCard = dto.getAccountRivalCard();
        String accountRivalBank = dto.getAccountRivalBank();


        String whichTable = caseManageService.getWhichTable(casemanageId);

        HashSet<Integer> idSet = new HashSet<>();
        String sql1="select BILL_ID id, to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') TRADE_DATE from " + whichTable +" where casemanage_id = '" + casemanageId+"' " ;
        if (sign !=null){
            sql1+=" and sign = "+sign;
        }
        if (StringUtils.isNotBlank(accountBodyCard)){
            sql1+=" and account_body_card = '"+accountBodyCard+"' ";
        }else{
            sql1+=" and 1=0  ";
        }
        if (StringUtils.isNotBlank(beginDate)){
            sql1+=" and TRADE_DATE >= to_date( '"+beginDate +"' , 'yyyy-mm-dd' ) ";
        }
        if (StringUtils.isNotBlank(endDate)){
            sql1+=" and TRADE_DATE <= to_date( '"+endDate +"' , 'yyyy-mm-dd' ) ";
        }
        sql1+=" order by trade_date ASC";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql1);
        for (int i = 0; i < list.size(); i++) {
            //第一条数据
            Map<String, Object> map1 = list.get(i);
            String tradedate1 = String.valueOf(map1.get("TRADE_DATE"));
            Integer ID1 = Integer.valueOf(map1.get("ID").toString());
            //第二条数据
            if (i+1>=list.size()){
                break;
            }
            Map<String, Object> map2 = list.get(i+1);
            String tradedate2 = String.valueOf(map2.get("TRADE_DATE"));
            Integer ID2 = Integer.valueOf(map2.get("ID").toString());
            try {
                Date date1 = df.parse(tradedate1);
                Date date2 = df.parse(tradedate2);
                long diff = date2.getTime() - date1.getTime();
                long hour = diff / (1000 * 60 * 60 );
                if (hour<=dtoHour){
                    idSet.add(ID1);
                    idSet.add(ID2);
                }
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }

        //第二个查询，根据第一条查询所得到的id集合为条件，得到最后的结果
        List<Integer> list_1 = new ArrayList<Integer>(idSet);
        List<List<Integer>> listList = ListUtils.splitList4(list_1, 1000);

        //返回的最终结果
        List<Map<String, Object>> bszizyDetailEntities = new ArrayList<>();
        if (listList!=null) {
            for (int i = 0; i < listList.size(); i++) {
                List<Integer> idList = listList.get(i);
                //第一个查询，查询出所有的交易记录，根据交易时间排序，然后过滤出交易时间为1小时之内的符合条件的记录。
                //存放符合条件的记录id
                String sql="select t.* from " + whichTable +" t where t.casemanage_id = '" + casemanageId+"' " ;
                if (sign !=null){
                    sql+=" and t.sign = "+sign;
                }
                if (StringUtils.isNotBlank(accountBodyCard)){
                    sql+=" and t.account_body_card = '"+accountBodyCard+"' ";
                }else{
                    sql+=" and 1=0  ";
                }
                if (StringUtils.isNotBlank(beginDate)){
                    sql+=" and t.TRADE_DATE >= to_date( '"+beginDate +"' , 'yyyy-mm-dd' ) ";
                }
                if (StringUtils.isNotBlank(endDate)){
                    sql+=" and t.TRADE_DATE <= to_date( '"+endDate +"' , 'yyyy-mm-dd' ) ";
                }
                if (StringUtils.isNotBlank(accountBody)){
                    sql+=" and t.account_body = '"+accountBody +"' ";
                }else{
                    sql+=" and t.account_body  is  null ";
                }
                if (StringUtils.isNotBlank(accountRival)){
                    sql+=" and t.account_rival = '"+accountRival +"' ";
                }else{
                    sql+=" and t.account_rival  is  null ";
                }
                if (StringUtils.isNotBlank(accountRivalCard)){
                    sql+=" and t.account_rival_card = '"+accountRivalCard +"' ";
                }else{
                    sql+=" and t.account_rival_card  is  null ";
                }
                if (StringUtils.isNotBlank(accountRivalBank)){
                    sql+=" and t.account_rival_bank = '"+accountRivalBank +"' ";
                }else{
                    sql+=" and t.account_rival_bank  is  null ";
                }
                sql+=" AND t.BILL_ID IN ("+StringUtils.join(idList,",")+") ";
                if (StringUtils.isNotBlank(sort)) {
                    sql+=" order by t." + sort + " " + order;
                }else{
                    sql+=" ORDER BY t.bill_id desc";
                }
                List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
                bszizyDetailEntities.addAll(maps);
            }
        }

       /* Query query = this.entityManager.createNativeQuery(sql);
        // 获取总结果集
        List<Map<String, Object>> maps = query.getResultList();
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setFirstResult((dto.getPage()-1)*dto.getRows());
        query.setMaxResults(dto.getRows());
        //获取查询结果集
        List<Map<String, Object>> list2 = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list2,null);*/
        return bszizyDetailEntities;
    }

    //伴随资金转移不同交易日期总天数 详情
    public List findBSZJZY_total_day_detail(BszizyDto dto){
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String casemanageId = dto.getCasemanageId();
        Integer sign = dto.getSign();
        Integer dtoHour = dto.getHour()==null?1:dto.getHour();

        String beginDate = dto.getBeginDate();
        String endDate = dto.getEndDate();
        String sort = dto.getSort();
        String order = dto.getOrder();

        String accountBodyCard = dto.getAccountBodyCard();
        String accountBody = dto.getAccountBody();
        String accountRival = dto.getAccountRival();
        String accountRivalCard = dto.getAccountRivalCard();
        String accountRivalBank = dto.getAccountRivalBank();


        String whichTable = caseManageService.getWhichTable(casemanageId);

        HashSet<Integer> idSet = new HashSet<>();
        String sql1="select BILL_ID id, to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') TRADE_DATE from " + whichTable +" where casemanage_id = '" + casemanageId+"' " ;
        if (sign !=null){
            sql1+=" and sign = "+sign;
        }
        if (StringUtils.isNotBlank(accountBodyCard)){
            sql1+=" and account_body_card = '"+accountBodyCard+"' ";
        }else{
            sql1+=" and 1=0  ";
        }
        if (StringUtils.isNotBlank(beginDate)){
            sql1+=" and TRADE_DATE >= to_date( '"+beginDate +"' , 'yyyy-mm-dd' ) ";
        }
        if (StringUtils.isNotBlank(endDate)){
            sql1+=" and TRADE_DATE <= to_date( '"+endDate +"' , 'yyyy-mm-dd' ) ";
        }
        sql1+=" order by trade_date ASC";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql1);
        for (int i = 0; i < list.size(); i++) {
            //第一条数据
            Map<String, Object> map1 = list.get(i);
            String tradedate1 = String.valueOf(map1.get("TRADE_DATE"));
            Integer ID1 = Integer.valueOf(map1.get("ID").toString());
            //第二条数据
            if (i+1>=list.size()){
                break;
            }
            Map<String, Object> map2 = list.get(i+1);
            String tradedate2 = String.valueOf(map2.get("TRADE_DATE"));
            Integer ID2 = Integer.valueOf(map2.get("ID").toString());
            try {
                Date date1 = df.parse(tradedate1);
                Date date2 = df.parse(tradedate2);
                long diff = date2.getTime() - date1.getTime();
                long hour = diff / (1000 * 60 * 60 );
                if (hour<=dtoHour){
                    idSet.add(ID1);
                    idSet.add(ID2);
                }
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }

        //第二个查询，根据第一条查询所得到的id集合为条件，得到最后的结果
        List<Integer> list_1 = new ArrayList<Integer>(idSet);
        List<List<Integer>> listList = ListUtils.splitList4(list_1, 1000);

        //返回的最终结果
        List<Map<String, Object>> bszizyDetailEntities = new ArrayList<>();
        if (listList!=null) {
            for (int i = 0; i < listList.size(); i++) {
                List<Integer> idList = listList.get(i);
                //第一个查询，查询出所有的交易记录，根据交易时间排序，然后过滤出交易时间为1小时之内的符合条件的记录。
                //存放符合条件的记录id
                String sql="select t.ACCOUNT_BODY,t.ACCOUNT_BODY_CARD,t.ACCOUNT_RIVAL,t.ACCOUNT_RIVAL_CARD,t.ACCOUNT_RIVAL_BANK , to_char( t.trade_date, 'yyyy-MM-dd' ) trade_date,count( 1 ) count,sum(abs( t.account_money )) trade_total_money from " + whichTable +" t where t.casemanage_id = '" + casemanageId+"' " ;
                if (sign !=null){
                    sql+=" and t.sign = "+sign;
                }
                if (StringUtils.isNotBlank(accountBodyCard)){
                    sql+=" and t.account_body_card = '"+accountBodyCard+"' ";
                }else{
                    sql+=" and 1=0  ";
                }
                if (StringUtils.isNotBlank(beginDate)){
                    sql+=" and t.TRADE_DATE >= to_date( '"+beginDate +"' , 'yyyy-mm-dd' ) ";
                }
                if (StringUtils.isNotBlank(endDate)){
                    sql+=" and t.TRADE_DATE <= to_date( '"+endDate +"' , 'yyyy-mm-dd' ) ";
                }
                if (StringUtils.isNotBlank(accountBody)){
                    sql+=" and t.account_body = '"+accountBody +"' ";
                }else{
                    sql+=" and t.account_body  is  null ";
                }
                if (StringUtils.isNotBlank(accountRival)){
                    sql+=" and t.account_rival = '"+accountRival +"' ";
                }else{
                    sql+=" and t.account_rival  is  null ";
                }
                if (StringUtils.isNotBlank(accountRivalCard)){
                    sql+=" and t.account_rival_card = '"+accountRivalCard +"' ";
                }else{
                    sql+=" and t.account_rival_card  is  null ";
                }
                if (StringUtils.isNotBlank(accountRivalBank)){
                    sql+=" and t.account_rival_bank = '"+accountRivalBank +"' ";
                }else{
                    sql+=" and t.account_rival_bank  is  null ";
                }
                sql+=" AND t.BILL_ID IN ("+StringUtils.join(idList,",")+") ";
                sql+=" GROUP BY to_char( trade_date, 'yyyy-MM-dd'),t.ACCOUNT_BODY,t.ACCOUNT_BODY_CARD,t.ACCOUNT_RIVAL,t.ACCOUNT_RIVAL_CARD,t.ACCOUNT_RIVAL_BANK  ";
                if (StringUtils.isNotBlank(sort)) {
                    sql+=" order by t." + sort + " " + order;
                }else{
                    sql+=" ORDER BY trade_date desc";
                }
                List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
                bszizyDetailEntities.addAll(maps);
            }
        }
        return bszizyDetailEntities;
    }

    /**
     * @Author 刘中华
     * @Date 2019/6/18 17:18
     * @Param
     * @return
     * @Description 伴随资金转移 优化之后的方法。用这个
     **/
    public List<BszizyEntity_YH> findBSZJZY_youhua2(BszizyDto dto){
        long start = System.currentTimeMillis();
        // 创建线程池
        ExecutorService pool = Executors.newFixedThreadPool(POOL_SIZE);
        //容器存放提交给线程池的任务,list,map,
        BlockingQueue<Future<List<BszizyEntity_YH>>> queue =
                new LinkedBlockingQueue<Future<List<BszizyEntity_YH>>>();
//        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String casemanageId = dto.getCasemanageId();
        Integer sign = dto.getSign();
//        Integer dtoHour = dto.getHour()==null?1:dto.getHour();
        Integer tradeCount = dto.getTradeCount()==null?3:dto.getTradeCount();
        String accountBodyCard = dto.getAccountBodyCard();
        String beginDate = dto.getBeginDate();
        String endDate = dto.getEndDate();
        String keyword = dto.getKeyword();
        String whichTable = caseManageService.getWhichTable(casemanageId);
        //第二 查询出和交易主体所有的交易记录
        String sql1="SELECT min(account_body) account_body,account_body_card,min(ACCOUNT_RIVAL) ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD, count(1) count ,to_char( MIN( TRADE_DATE ), 'yyyy-mm-dd' ) min_trade_time,to_char( MAX( TRADE_DATE ), 'yyyy-mm-dd' ) max_trade_time ";
        sql1+=" FROM "+ whichTable;
        sql1+=" WHERE ACCOUNT_RIVAL_CARD IS NOT NULL ";
        if (sign !=null){
            sql1+=" and sign = "+sign;
        }
        if (StringUtils.isNotBlank(accountBodyCard)){
            sql1+=" and account_body_card = '"+accountBodyCard+"' ";
        }else{
            sql1+=" and 1=0 ";
        }
        if (StringUtils.isNotBlank(beginDate)){
            sql1+=" AND TRADE_DATE >= TO_DATE('" +beginDate +"', 'yyyy-mm-dd')";
        }
        if (StringUtils.isNotBlank(endDate)){
            sql1+=" AND TRADE_DATE <= TO_DATE('" +endDate +"', 'yyyy-mm-dd')";
        }
        if (StringUtils.isNotBlank(keyword)){
            sql1+=" and ( ACCOUNT_RIVAL_CARD LIKE '%"+keyword+"%' or ACCOUNT_RIVAL LIKE '%"+keyword+"%') ";
        }
        sql1+=" GROUP BY account_body_card,ACCOUNT_RIVAL_CARD ORDER BY count(1) desc";
        List<Map<String, Object>> sql1_list = jdbcTemplate.queryForList(sql1);
        //第二阶段，开始遍历除当前交易之外的伴随交易
        List<BszizyEntity_YH> result = new ArrayList<>();
        System.out.println("需要循环"+sql1_list.size()+"次");
        for (int i = 0; i < sql1_list.size(); i++) {
            Map<String, Object> map = sql1_list.get(i);
            String account_rival_card = (String)map.get("ACCOUNT_RIVAL_CARD");
            String account_rival= (String)map.get("ACCOUNT_RIVAL");
            Integer count= ((BigDecimal)map.get("COUNT")).intValue();
            //计算出一小时时间间隔范围
            String s=getSqlTime(dto,account_rival_card);

            String sql2="SELECT min(account_body) ACCOUNT_BODY,account_body_card ACCOUNT_BODY_CARD,min(ACCOUNT_RIVAL) ACCOUNT_RIVAL2,ACCOUNT_RIVAL_CARD ACCOUNT_RIVAL_CARD2, count(1) COUNT";
            sql2+=" FROM " + whichTable;
            sql2+=" WHERE ACCOUNT_RIVAL_CARD IS NOT NULL ";
            if (sign !=null){
                sql2+=" and sign = "+sign;
            }
            if (StringUtils.isNotBlank(accountBodyCard)){
                sql2+=" and account_body_card = '"+accountBodyCard+"' ";
            }else{
                sql2+=" and 1=0 ";
            }
            sql2+=" AND ACCOUNT_RIVAL_CARD != '" +account_rival_card +"' ";
            if (StringUtils.isNotBlank(s)){
                sql2+=s;
            }else{
                sql2+=" and 1=0 ";
            }
            sql2+=" GROUP BY account_body_card,ACCOUNT_RIVAL_CARD ";
            sql2+=" HAVING count(DISTINCT to_char( trade_date, 'yyyy-MM-dd' )) >= " + tradeCount;
            sql2+=" ORDER BY count(1) desc";
            Future<List<BszizyEntity_YH>> future = pool.submit(new BszjzyWorkTask(sql2,jdbcTemplate,account_rival_card,account_rival,sign,count));
            queue.add(future);
            //System.out.println("循环"+i);
        }
        // 检查线程池任务执行结果
        for (int i = 0; i < sql1_list.size(); i++) {
            try {
                List<BszizyEntity_YH> bszizyEntities = queue.take().get();
                result.addAll(bszizyEntities);
            } catch (InterruptedException e) {
                e.printStackTrace();
            } catch (ExecutionException e) {
                e.printStackTrace();
            }
        }
        //排序
        Collections.sort(result, new Comparator<BszizyEntity_YH>() {
            @Override
            public int compare(BszizyEntity_YH o1, BszizyEntity_YH o2) {
                int c = o2.getC() - o1.getC();
                if (c == 0){
                    if (o2.getC()>1){
                        String account_rival_card = o2.getACCOUNT_RIVAL_CARD();
                        String account_rival_card1 = o1.getACCOUNT_RIVAL_CARD();
                        if (account_rival_card.compareTo(account_rival_card1)==0){
                            return o2.getCOUNT() - o1.getCOUNT();
                        }else{
                            return account_rival_card.compareTo(account_rival_card1);
                        }
                    }else{
                        return o2.getCOUNT() - o1.getCOUNT();
                    }
                }else{
                    return c;
                }
            }
        });
        System.out.println("本次查询花费"+(System.currentTimeMillis()-start)/1000+" s");
        return result;
    }

    public List<Map<String, Object>> findBSZJZY_detail_youhua2(BszizyDto dto){
        String casemanageId = dto.getCasemanageId();
        Integer sign = dto.getSign();
        String whichTable = caseManageService.getWhichTable(casemanageId);
        String beginDate = dto.getBeginDate();
        String endDate = dto.getEndDate();
        String sort = dto.getSort();
        String order = dto.getOrder();

        String accountBodyCard = dto.getAccountBodyCard();
        String accountBody = dto.getAccountBody();
        String accountRival = dto.getAccountRival();
        String accountRivalCard = dto.getAccountRivalCard();
        String accountRivalCard2 = dto.getAccountRivalCard2();//伴随卡号
        String accountRivalBank = dto.getAccountRivalBank();

        String s = getSqlTime(dto, accountRivalCard);
        //返回的最终结果
        List<Map<String, Object>> bszizyDetailEntities = new ArrayList<>();
        //第一个查询，查询出所有的交易记录，根据交易时间排序，然后过滤出交易时间为1小时之内的符合条件的记录。
        //存放符合条件的记录id
        String sql="select t.* from " + whichTable +" t where t.casemanage_id = '" + casemanageId+"' " ;
        if (sign !=null){
            sql+=" and t.sign = "+sign;
        }
        if (StringUtils.isNotBlank(accountBodyCard)){
            sql+=" and t.account_body_card = '"+accountBodyCard+"' ";
        }else{
            sql+=" and 1=0  ";
        }
        if (StringUtils.isNotBlank(beginDate)){
            sql+=" and t.TRADE_DATE >= to_date( '"+beginDate +"' , 'yyyy-mm-dd' ) ";
        }
        if (StringUtils.isNotBlank(endDate)){
            sql+=" and t.TRADE_DATE <= to_date( '"+endDate +"' , 'yyyy-mm-dd' ) ";
        }
        if (StringUtils.isNotBlank(accountRivalCard2)){
            sql+=" and t.account_rival_card = '"+accountRivalCard2 +"' ";
        }else{
            sql+=" and t.account_rival_card  is  null ";
        }
        if (StringUtils.isNotBlank(accountRivalBank)){
            sql+=" and t.account_rival_bank = '"+accountRivalBank +"' ";
        }
        if (StringUtils.isNotBlank(s)){
            sql+=s;
        }else{
            sql+=" and 1=0 ";
        }
        if (StringUtils.isNotBlank(sort)) {
            sql+=" order by t." + sort + " " + order;
        }else{
            sql+=" ORDER BY t.TRADE_DATE desc";
        }
        bszizyDetailEntities = jdbcTemplate.queryForList(sql);
        return bszizyDetailEntities;
    }
    //把符合条件的id抽离出来
    public HashSet<Integer> getID(BszizyDto dto, String whichTable){
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String casemanageId = dto.getCasemanageId();
        Integer sign = dto.getSign();
        Integer dtoHour = dto.getHour()==null?1:dto.getHour();
        String accountBodyCard = dto.getAccountBodyCard();
        String beginDate = dto.getBeginDate();
        String endDate = dto.getEndDate();
        String keyword = dto.getKeyword();
        HashSet<Integer> idSet = new HashSet<>();
        String sql1="select BILL_ID id, to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') TRADE_DATE from " + whichTable +" where casemanage_id = '" + casemanageId+"' " ;
        if (sign !=null){
            sql1+=" and sign = "+sign;
        }
        if (StringUtils.isNotBlank(accountBodyCard)){
            sql1+=" and account_body_card = '"+accountBodyCard+"' ";
        }else{
            sql1+=" and 1=0  ";
        }
        /*if (StringUtils.isNotBlank(keyword)){
            sql1+=" and ( ACCOUNT_RIVAL_CARD like '%"+keyword+"%' or ACCOUNT_RIVAL like '%"+keyword+"%') ";
        }*/
        if (StringUtils.isNotBlank(beginDate)){
            sql1+=" and TRADE_DATE >= to_date( '"+beginDate +"' , 'yyyy-mm-dd' ) ";
        }
        if (StringUtils.isNotBlank(endDate)){
            sql1+=" and TRADE_DATE <= to_date( '"+endDate +"' , 'yyyy-mm-dd' ) ";
        }
        sql1+=" order by trade_date ASC";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql1);
        for (int i = 0; i < list.size(); i++) {
            //第一条数据
            Map<String, Object> map1 = list.get(i);
            String tradedate1 = String.valueOf(map1.get("TRADE_DATE"));
            Integer ID1 = Integer.valueOf(map1.get("ID").toString());
            //第二条数据
            if (i+1>=list.size()){
                break;
            }
            Map<String, Object> map2 = list.get(i+1);
            String tradedate2 = String.valueOf(map2.get("TRADE_DATE"));
            Integer ID2 = Integer.valueOf(map2.get("ID").toString());
            try {
                Date date1 = df.parse(tradedate1);
                Date date2 = df.parse(tradedate2);
                long diff = date2.getTime() - date1.getTime();
                long hour = diff / (1000 * 60 * 60 );
                if (hour<=dtoHour){
                    idSet.add(ID1);
                    idSet.add(ID2);
                }
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        return idSet;
    }

    public String getSqlTime(BszizyDto dto,String account_rival_card){
        if (StringUtils.isBlank(account_rival_card)) {
            return "and (1=1)";
        }
        Integer sign = dto.getSign();
        String casemanageId = dto.getCasemanageId();
        String accountBodyCard = dto.getAccountBodyCard();
        String beginDate = dto.getBeginDate();
        String endDate = dto.getEndDate();
        String whichTable = caseManageService.getWhichTable(casemanageId);

        String sql_time="SELECT TO_CHAR( TRADE_DATE-1/24, 'yyyy-mm-dd hh24:mi:ss' ) min_time,TO_CHAR( TRADE_DATE+1/24, 'yyyy-mm-dd hh24:mi:ss' ) max_time  FROM " + whichTable;
        sql_time+=" WHERE TRADE_DATE is not NULL ";
        sql_time+=" and sign = "+sign;
        sql_time+=" and account_body_card = '"+accountBodyCard+"' ";
        sql_time+=" and ACCOUNT_RIVAL_CARD = '"+account_rival_card+"' ";
        if (StringUtils.isNotBlank(beginDate)){
            sql_time+=" AND TRADE_DATE >= TO_DATE('" +beginDate +"', 'yyyy-mm-dd')";
        }
        if (StringUtils.isNotBlank(endDate)){
            sql_time+=" AND TRADE_DATE <= TO_DATE('" +endDate +"', 'yyyy-mm-dd')";
        }
        sql_time+=" ORDER BY TRADE_DATE";
        List<Map<String, Object>> sql_timeList = jdbcTemplate.queryForList(sql_time);

        String s=" and ( ";
        if (sql_timeList.size()>0){
            for (int j = 0; j < sql_timeList.size()-1; j++) {
                Map<String, Object> sql_timeItem = sql_timeList.get(j);
                String min_time = (String)sql_timeItem.get("min_time");
                String max_time = (String)sql_timeItem.get("max_time");
                s+="( TO_CHAR(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss')>='"+min_time+"' and TO_CHAR(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss')<='"+max_time+"' ) OR ";
            }
            String min_time2 =(String)sql_timeList.get(sql_timeList.size()-1).get("min_time");
            String max_time2 =(String)sql_timeList.get(sql_timeList.size()-1).get("max_time");
            s+="( TO_CHAR(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss')>='"+min_time2+"' and TO_CHAR(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss')<='"+max_time2+"' ) )";
        }else{
            s="";
        }
        return s;
    }
    public static void main(String[] args){

    }
}
